package dao.impl;

import dao.StudentDao;
import domain.Student;
import utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 于金鹏
 * @Date 2024/12/12 16:28
 * @Package yujinpeng
 */
public class StudentDaoImpl implements StudentDao {
    @Override
    public List<Student> list(Student student) throws SQLException {
        Connection conn = JdbcUtils.getConnection();

        String sql = "select * from student where 1=1 ";
        if (student.getName() != null && !student.getName().isEmpty()) {
            sql += " and name like ?";
        }

        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        int i = 1;
        if (student.getName() != null && !student.getName().isEmpty()) {
            preparedStatement.setString(i++, "%" + student.getName() + "%");
        }

        // 执行sql
        ResultSet rs = preparedStatement.executeQuery();
        List<Student> list = new ArrayList<Student>();
        while (rs.next()) {
            list.add(new Student(
                    rs.getInt("id"),
                    rs.getString("name"),
                    rs.getInt("age"),
                    rs.getInt("sex"),
                    rs.getString("phone")
            ));
        }
        preparedStatement.close();
        conn.close();
        return list;
    }

    @Override
    public Student getStudent(int id) {
        return null;
    }

    @Override
    public int addStudent(Student student) throws SQLException {
        Connection conn = JdbcUtils.getConnection();
        String sql = "INSERT INTO student (name, age, sex, phone) VALUES (?,?,?,?)";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1, student.getName());
        preparedStatement.setInt(2, student.getAge());
        preparedStatement.setInt(3, student.getSex());
        preparedStatement.setString(4, student.getPhone());
        preparedStatement.close();
        conn.close();
        return 0;
    }

    @Override
    public int updateStudent(Student student) throws SQLException {
        Connection conn = JdbcUtils.getConnection();
        String sql = "UPDATE student SET name =?, age =?, sex =?, phone =? WHERE id =?";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1, student.getName());
        preparedStatement.setInt(2, student.getAge());
        preparedStatement.setInt(3, student.getSex());
        preparedStatement.setString(4, student.getPhone());
        preparedStatement.setInt(5, student.getId());
        preparedStatement.executeUpdate();
        return 0;
    }

    @Override
    public int deleteStudent(int id) throws SQLException {
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        conn = JdbcUtils.getConnection();
        String sql = "DELETE FROM student WHERE id =?";
        preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setInt(1, id);
        preparedStatement.executeUpdate();
        preparedStatement.close();
        conn.close();
        return 0;
    }
}
